﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Database
{

    public class CT_DonDatHangLK
    {

        public int Maddh { get; set; }
        public int MaLinhKien { get; set; }
        public int SoLuong { get; set; }
        public double GiaBan { get; set; }

        public CT_DonDatHangLK()
        {
        }
        public CT_DonDatHangLK(int maddh, int malinhkien, int soluong, double giaban)
        {
            this.Maddh = maddh;
            this.MaLinhKien = malinhkien;
            this.SoLuong = soluong;
            this.GiaBan = giaban;
        }

        /// <summary>
        /// Lấy chi tiết đơn đặt hàng theo khách hàng
        /// </summary>
        /// <returns></returns>
        public DataTable DSCT_DonDatHangLKCuaKH(int maddh)
        {
            DataTable ds = new DataTable();
            try
            {
                string query = "select sum(ct.giaban*ct.soluong) as tongtien,ddh.tenkh,ddh.ngaydathang,ddh.tinhtrang,ct.soluong,ct.giaban,mt.tenmay "
                                + " from ct_dondathang ct,dondathang ddh,maytinh mt "
                                + " where ct.mamay=mt.mamay and ct.maddh=ddh.maddh and ct.maddh=" + maddh + " "
                                + " group by ct.maddh,ddh.tenkh,ddh.ngaydathang,ddh.tinhtrang,ct.soluong,ct.giaban,mt.tenmay";
                sqlConnection db = new sqlConnection();
                SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return ds;
        }


        public void ThemCT_DonDatHangLK(CT_DonDatHangLK add)
        {

            SqlCommand cmd;
            try
            {
                sqlConnection db = new sqlConnection();
                cmd = new SqlCommand("insert into CT_DonDatHangLK(maddh,malinhkien,soluong,giaban) values(@maddh,@malinhkien,@soluong,@giaban)", db.GetConnect());
                cmd.Parameters.Add("@maddh", SqlDbType.Int).Value = add.Maddh;
                cmd.Parameters.Add("@malinhkien", SqlDbType.Int).Value = add.MaLinhKien;
                cmd.Parameters.Add("@soluong", SqlDbType.Int).Value = add.SoLuong;
                cmd.Parameters.Add("@giaban", SqlDbType.Float).Value = add.GiaBan;

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

        public void XoaCT_DonDatHang(CT_DonDatHang delete)
        {
            SqlCommand cmd;
            try
            {
                sqlConnection db = new sqlConnection();
                cmd = new SqlCommand("delete ct_dondathang where maddh=@maddh and mamay=@mamay", db.GetConnect());
                cmd.Parameters.Add("@maddh", SqlDbType.Int).Value = delete.Maddh;
                cmd.Parameters.Add("@mamay", SqlDbType.Int).Value = delete.Mamay;

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public void SuaCT_DonDatHang(CT_DonDatHang update)
        {
            SqlCommand cmd;
            try
            {
                sqlConnection db = new sqlConnection();
                cmd = new SqlCommand("update ct_dondathang set soluong=@soluong,giaban=@giaban where maddh=@maddh and mamay=@mamay", db.GetConnect());
                cmd.Parameters.Add("@maddh", SqlDbType.Int).Value = update.Maddh;
                cmd.Parameters.Add("@mamay", SqlDbType.Int).Value = update.Mamay;
                cmd.Parameters.Add("@soluong", SqlDbType.Int).Value = update.Soluong;
                cmd.Parameters.Add("@giaban", SqlDbType.Float).Value = update.Giaban;

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

        public DataTable DanhSachCT_DonDatHang()
        {
            DataTable ds = new DataTable();
            try
            {
                string query = "select * from ct_dondathang ";
                sqlConnection db = new sqlConnection();
                SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return ds;
        }

        public DataTable DangSachCT_DDHTheoMa(int maddh, int mamay)
        {
            DataSet ds = new DataSet();
            try
            {
                string query = string.Format("select ct.* from ct_dondathang ct where ct.maddh={0} and ct.mamay={1}", maddh, mamay);
                sqlConnection db = new sqlConnection();
                SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
                sda.Fill(ds);
            }
            catch
            {
                ds = null;
            }
            return ds.Tables[0];
        }
        public static DataSet DSCTDDHLK()
        {
            DataSet ds = new DataSet();
            string query = "select*from ct_dondathanglk";
            sqlConnection db = new sqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
            sda.Fill(ds);
            return ds;
        }
        public static DataSet CTDDHLKTheoMa(int maddh)
        {
            DataSet ds = new DataSet();
            string query = "select*from ct_dondathanglk where maddh="+maddh;
            sqlConnection db = new sqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
            sda.Fill(ds);
            return ds;
        }
        public bool CapNhatCTDDHLK(int maddh, int malinhkien, int soluong, float giaban)
        {
            bool kq = true;

            try
            {
                string query = "update ct_dondathanglk set soluong='{0}',giaban='{1}'where maddh='{2}' and malinhkien='{3}'";
                query = String.Format(query, soluong, giaban, maddh, malinhkien);
                sqlConnection db = new sqlConnection();
                SqlCommand cmd = new SqlCommand(query, db.GetConnect());
                return cmd.ExecuteNonQuery() > 0;
            }
            catch
            {
                kq = false;
            }

            return kq;
        }
        public bool XoaCTDDHLK(int maddh,int malinhkien)
        {
            bool kq = true;

            try
            {
                string query = "Delete ct_dondathanglk where maddh='{0}'and malinhkien='{1}'";
                query = String.Format(query, maddh,malinhkien);
                sqlConnection db = new sqlConnection();
                SqlCommand cmd = new SqlCommand(query, db.GetConnect());
                cmd.ExecuteNonQuery();
            }
            catch
            {
                kq = false;
            }

            return kq;
        }
        public bool XoaCTDDHLKTheoMa(int maddh)
        {
            bool kq = true;

            try
            {
                string query = "Delete ct_dondathanglk where maddh='{0}'";
                query = String.Format(query, maddh);
                sqlConnection db = new sqlConnection();
                SqlCommand cmd = new SqlCommand(query, db.GetConnect());
                cmd.ExecuteNonQuery();
            }
            catch
            {
                kq = false;
            }

            return kq;
        }
        public CT_DonDatHangLK ThongTinCTDDHLK(int maddh)
        {
            string query = "select * from ct_dondathanglk where maddh=" + maddh;
            sqlConnection db = new sqlConnection();
            SqlCommand sc = new SqlCommand(query, db.GetConnect());
            SqlDataReader reader = sc.ExecuteReader();
            CT_DonDatHangLK ctddh = new CT_DonDatHangLK();
            while (reader.Read())
            {
                ctddh.Maddh = reader.GetInt32(0);
                ctddh.MaLinhKien = reader.GetInt32(1);
                ctddh.SoLuong = reader.GetInt32(2);
                ctddh.GiaBan = reader.GetDouble(3);
            }
            return ctddh;
        }

    }
}
